package ext.tianma.part.util;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.rmi.RemoteException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONException;

import ext.tianma.MaterialClassification.CreatePartClassificationUtil;
import ext.tianma.bom.eco.BOMHelp;
import ext.tianma.bom.util.ClassificationUtil;
import ext.tianma.bom.util.QueryUtil;
import ext.tianma.util.IbaAttributeUtil;
import wt.csm.navigation.ClassificationNode;
import wt.fc.ObjectIdentifier;
import wt.fc.ObjectReference;
import wt.fc.PersistenceHelper;
import wt.fc.QueryResult;
import wt.iba.definition.ReferenceDefinition;
import wt.iba.value.IBAHolder;
import wt.iba.value.IBAReferenceable;
import wt.iba.value.ReferenceValue;
import wt.lifecycle.LifeCycleHelper;
import wt.lifecycle.State;
import wt.part.WTPart;
import wt.part.WTPartMaster;
import wt.part.WTPartStandardConfigSpec;
import wt.query.KeywordExpression;
import wt.query.QuerySpec;
import wt.query.SearchCondition;
import wt.util.WTException;
import wt.util.WTPropertyVetoException;
import wt.vc.config.ConfigHelper;
import wt.vc.views.View;
import wt.vc.views.ViewException;
import wt.vc.views.ViewHelper;
import wt.vc.views.ViewManageable;
import wt.workflow.engine.WfProcess;

public class ExportPartByIBAValue {
	public static String STATUTE_PATH = "/ptc/Windchill/codebase/ext/tianma/cfg/exportParts.xlsx";
	public static String PART_STATUTE_PATH = "/ptc/Windchill/codebase/ext/tianma/part/config/partState.xlsx";
	private final static String PATH = "/ext/tianma/util/queryClassificationPath.xml";
	public static String softtype_finishgood = "cn.tianma.FinishedGood";// 成品
	public static String softtype_semiFinishgood = "cn.tianma.SemiFinishedGood";// 半成品
	public static String softtype_semiFinishgood_C4 = "cn.tianma.C4";// 半成品——C4
	public static String softtype_semiFinishgood_CF = "cn.tianma.CF";// 半成品——CF
	public static String softtype_semiFinishgood_XP = "cn.tianma.XP";// 半成品——XP
	public static String softtype_semiFinishgood_SP = "cn.tianma.SP";// 半成品——SP
	public static String softtype_RawMaterial = "cn.tianma.RawMaterial";// 上偏光片
	public static String softtype_RawMaterial_TopPolarizer = "cn.tianma.TopPolarizer";// 原材料
	public static String softtype_RawMaterial_BotPolarizerl = "cn.tianma.BotPolarizerl";// 下偏光片
	public static String softtype_PhantomPolarizer = "cn.tianma.PhantomPolarizer";// 虚拟偏光片组件

	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";
	private static final int[] DEFAULT_CONDITION_ARRAY = new int[] { 0 };

	/***
	 * 获取系统中所有成品数据 gx
	 * 
	 * @throws ViewException
	 * @throws WTException
	 */
	public static void ExportExportPartByIBAValues() throws ViewException, WTException {
		
		System.out.println("===>>start==>>writeToExcel===>>");
		InputStream is = null;
		OutputStream on = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;
//			Timestamp date = Timestamp.valueOf("2019-9-15");
			Timestamp date = Timestamp.valueOf("2019-9-15 00:00:00");	
			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Cell cell1;

			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);

			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, softtype_finishgood);
			// IBA 属性过滤
			System.out.println();
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			SearchCondition sc7 = new SearchCondition(WTPart.class, "thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL, date);
			qs.appendAnd();
			qs.appendSearchCondition(sc7);
					
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
			// 第一行为表头，从第二行开始遍历
			for (int i = 1; i <= qr.size(); i++) {
				System.out.println("=====已写入 第" + i + "条数据====");
				Row row = sheet.createRow(i);
				part = (WTPart) qr.nextElement();
				System.out.println("part======>>>" + part.getNumber());
				IBAUtil iba = new IBAUtil(part);
				String FinishedGoodType = iba.getIBAValue("FinishedGoodType"); //
				System.out.println("FinishedGoodType====>>" + FinishedGoodType);
				String finishedGoodLCDType = iba.getIBAValue("finishedGoodLCDType");// LCD 型号
				String partRFQNumber = iba.getIBAValue("partRFQNumber");// RFQ编号
				String resolutiontype = iba.getIBAValue("resolutiontype");// 分辨率类别
				String touchpanel = iba.getIBAValue("touchpanel");// 触控面板
				String structure = iba.getIBAValue("structure");// 结构
				String finishedGoodDimension = iba.getIBAValue("finishedGoodDimension");// 产品维度
				String patentClaims = iba.getIBAValue("patentClaims ");// 专利要求
				String finishedGoodUse = iba.getIBAValue("finishedGoodUse");// 产品用途
				String marketSegments = iba.getIBAValue("marketSegments");// 细分市场
				String modulesDevelopedLand = iba.getIBAValue("modulesDevelopedLand");// 模组研发地
				String productWeight = iba.getIBAValue("productWeight");// 产品净重(g)
				String finishedGoodModuleSize = iba.getIBAValue("finishedGoodModuleSize");// 模组尺寸
				String PanelType = iba.getIBAValue("PanelType");// PanelType
				String duty = iba.getIBAValue("duty");// duty
				String finishedGoodGlassThickness = iba.getIBAValue("finishedGoodGlassThickness");// finishedGoodGlassThickness
				String panelPlaceOfOrigin = iba.getIBAValue("panelPlaceOfOrigin");// panelPlaceOfOrigin
				String panelDevelopedLand = iba.getIBAValue("panelDevelopedLand");// panel研发地
				String moduleOrigin = iba.getIBAValue("moduleOrigin ");// 模组产地
				String TPlensLaminatingOrigin = iba.getIBAValue("TPlensLaminatingOrigin");// 模组产地
				String customerName = iba.getIBAValue("customerName");// 客户名称
				String partMainSpecification = iba.getIBAValue("partMainSpecification");// 产品规格
				String centerBrightnessnits = iba.getIBAValue("centerBrightnessnits");// 中心亮度nits
				String whiteCenterCoordiates = iba.getIBAValue("whiteCenterCoordiates");// 中心白坐标
				String Interface = iba.getIBAValue("Interface");// Interface
				String ICType = iba.getIBAValue("ICType");// IC型号
				String ConnectorType = iba.getIBAValue("ConnectorType");// Connector Type
				String ModuleTransmittance = iba.getIBAValue("ModuleTransmittance");// 模组穿透率
				String PolarizerType = iba.getIBAValue("PolarizerType");// 偏光片类型
				String GrayscaleReverseDirection = iba.getIBAValue("GrayscaleReverseDirection");// 灰阶反转方向
				String PCBAType = iba.getIBAValue("PCBAType");// PCBA Type(料号)
				String IDPin = iba.getIBAValue("IDPin");// ID Pin
				String Power = iba.getIBAValue("Power");// Power
				String pinNumber = iba.getIBAValue("pinNumber");// pinNumber
				String NTSC = iba.getIBAValue("NTSC");// NTSC
				String Contrast = iba.getIBAValue("Contrast");// 对比度
				String panelTransmittance = iba.getIBAValue("panelTransmittance");// panel穿透率
				String ResponseTime = iba.getIBAValue("ResponseTime");// 响应时间
				String PalletPacking = iba.getIBAValue("PalletPacking");// PalletPacking
				String finishedGoodResolutionH = iba.getIBAValue("finishedGoodResolutionH");// 分辨率(横)[只能是整数]
				String finishedGoodResolutionV = iba.getIBAValue("finishedGoodResolutionV");// 分辨率(竖)[只能是整数]
				String finishedGoodResolution = iba.getIBAValue("finishedGoodResolution");// 分辨率(横x竖)
				String finishedGoodDisplayMode = iba.getIBAValue("finishedGoodDisplayMode");// 显示模式
				String finishedGoodLineBody = iba.getIBAValue("finishedGoodLineBody");// 线体
				String partEnvProtectionCategory = iba.getIBAValue("partEnvProtectionCategory");//
				String partInspectionStandard = iba.getIBAValue("partInspectionStandard");// 检验标准
				String finishedGoodMaskNumber = iba.getIBAValue("finishedGoodMaskNumber");// Mask张数
				String finishedGoodSize = iba.getIBAValue("finishedGoodSize");// 产品尺寸(inch)
				String TFTMODELID = iba.getIBAValue("TFTMODELID");// TFT MODEL
				String LCDArrangement = iba.getIBAValue("LCDArrangement");// LCD排版数
				String finishedGoodModel = iba.getIBAValue("finishedGoodModel");// Model(
				String partDescription = iba.getIBAValue("partDescription");// 物料描述
				String specialMonitor = iba.getIBAValue("specialMonitor");// 是否专显
				String materialDetails = iba.getIBAValue("materialDetails");// 物料详细说明
				String productClassification = iba.getIBAValue("productClassification");// 产品分级
				String Integration = iba.getIBAValue("Integration");// 集成度
				String partNote = iba.getIBAValue("partNote");// 备注说明
				String partNumberEffectiveSite = iba.getIBAValue("partNumberEffectiveSite");// 备注说明
				System.out.println("partNumberEffectiveSite===>>" + partNumberEffectiveSite);
				List<String> effectiveSite = IbaAttributeUtil.getAttributeValues(part, "partNumberEffectiveSite");
				String partState = part.getLifeCycleState().getDisplay(Locale.CHINA);
				String effectiveSites = "";
				if (effectiveSite != null) {
					for (String str : effectiveSite) {
						effectiveSites += str;
					}
				}
				System.out.println("===>>>" + effectiveSites);

				for (int k = 0; k <= 62; k++) {
					// 在一行内循环
					Cell first = row.createCell(0);
					first.setCellValue(part.getNumber());

					Cell second = row.createCell(1);
					second.setCellValue(partState);

					Cell third = row.createCell(2);
					third.setCellValue(part.getName());

					Cell cell4 = row.createCell(3);
					cell4.setCellValue(part.getSource().toString());

					Cell cell5 = row.createCell(4);
					cell5.setCellValue(part.getDefaultUnit().toString());

					Cell cell6 = row.createCell(5);
					cell6.setCellValue(finishedGoodLCDType);

					Cell cell7 = row.createCell(6);
					cell7.setCellValue(partRFQNumber);

					Cell cell8 = row.createCell(7);
					cell8.setCellValue(resolutiontype);

					Cell cell9 = row.createCell(8);
					cell9.setCellValue(touchpanel);

					Cell cell10 = row.createCell(9);
					cell10.setCellValue(structure);

					Cell cell11 = row.createCell(10);
					cell11.setCellValue(finishedGoodDimension);

					Cell cell12 = row.createCell(11);
					cell12.setCellValue(patentClaims);

					Cell cell13 = row.createCell(12);
					cell13.setCellValue(finishedGoodUse);

					Cell cell14 = row.createCell(13);
					cell14.setCellValue(marketSegments);

					Cell cell15 = row.createCell(14);
					cell15.setCellValue(modulesDevelopedLand);

					Cell cell16 = row.createCell(15);
					cell16.setCellValue(productWeight);

					Cell cell17 = row.createCell(16);
					cell17.setCellValue(finishedGoodModuleSize);

					Cell cell18 = row.createCell(17);
					cell18.setCellValue(PanelType);

					Cell cell19 = row.createCell(18);
					cell19.setCellValue(duty);

					Cell cell20 = row.createCell(19);
					cell20.setCellValue(finishedGoodGlassThickness);

					Cell cell21 = row.createCell(20);
					cell21.setCellValue(panelPlaceOfOrigin);

					Cell cell22 = row.createCell(21);
					cell22.setCellValue(panelDevelopedLand);

					Cell cell23 = row.createCell(22);
					cell23.setCellValue(moduleOrigin);

					Cell cell24 = row.createCell(23);
					cell24.setCellValue(TPlensLaminatingOrigin);

					Cell cell25 = row.createCell(24);
					cell25.setCellValue(customerName);

					Cell cell26 = row.createCell(25);
					cell26.setCellValue(partMainSpecification);

					Cell cell27 = row.createCell(26);
					cell27.setCellValue(centerBrightnessnits);

					Cell cell28 = row.createCell(27);
					cell28.setCellValue(whiteCenterCoordiates);

					Cell cell29 = row.createCell(28);
					cell29.setCellValue(Interface);

					Cell cell30 = row.createCell(29);
					cell30.setCellValue(ICType);
					Cell cell31 = row.createCell(30);
					cell31.setCellValue(ConnectorType);

					Cell cell32 = row.createCell(31);
					cell32.setCellValue(ModuleTransmittance);

					Cell cell33 = row.createCell(32);
					cell33.setCellValue(PolarizerType);

					Cell cell34 = row.createCell(33);
					cell34.setCellValue(GrayscaleReverseDirection);

					Cell cell35 = row.createCell(34);
					cell35.setCellValue(PCBAType);

					Cell cell36 = row.createCell(35);
					cell36.setCellValue(IDPin);

					Cell cell37 = row.createCell(36);
					cell37.setCellValue(Power);

					Cell cell38 = row.createCell(37);
					cell38.setCellValue(pinNumber);

					Cell cell39 = row.createCell(38);
					cell39.setCellValue(NTSC);

					Cell cell40 = row.createCell(39);
					cell40.setCellValue(Contrast);

					Cell cell41 = row.createCell(40);
					cell41.setCellValue(panelTransmittance);

					Cell cell42 = row.createCell(41);
					cell42.setCellValue(ResponseTime);

					Cell cell43 = row.createCell(42);
					cell43.setCellValue(PalletPacking);

					Cell cell44 = row.createCell(43);
					cell44.setCellValue(finishedGoodResolutionH);

					Cell cell45 = row.createCell(44);
					cell45.setCellValue(finishedGoodResolutionV);

					Cell cell46 = row.createCell(45);
					cell46.setCellValue(finishedGoodResolution);

					Cell cell47 = row.createCell(46);
					cell47.setCellValue(finishedGoodDisplayMode);

					Cell cell48 = row.createCell(47);
					cell48.setCellValue(finishedGoodLineBody);

					Cell cell49 = row.createCell(48);
					cell49.setCellValue(partEnvProtectionCategory);

					Cell cell50 = row.createCell(49);
					cell50.setCellValue(partInspectionStandard);

					Cell cell51 = row.createCell(50);
					cell51.setCellValue(finishedGoodMaskNumber);

					Cell cell52 = row.createCell(51);
					cell52.setCellValue(finishedGoodSize);

					Cell cell53 = row.createCell(52);
					cell53.setCellValue(TFTMODELID);

					Cell cell54 = row.createCell(53);
					cell54.setCellValue(LCDArrangement);

					Cell cell55 = row.createCell(54);
					cell55.setCellValue(FinishedGoodType);

					Cell cell56 = row.createCell(55);
					cell56.setCellValue(finishedGoodModel);

					Cell cell57 = row.createCell(56);
					cell57.setCellValue(partDescription);

					Cell cell58 = row.createCell(57);
					cell58.setCellValue(specialMonitor);

					Cell cell59 = row.createCell(58);
					cell59.setCellValue(materialDetails);

					Cell cell60 = row.createCell(59);
					cell60.setCellValue(productClassification);

					Cell cell61 = row.createCell(60);
					cell61.setCellValue(Integration);

					Cell cell62 = row.createCell(61);
					cell62.setCellValue(partNote);

					Cell cell63 = row.createCell(62);
					cell63.setCellValue(effectiveSites);
				}

			}
			on = new FileOutputStream(STATUTE_PATH);
			workbook.write(on);
			// while (qr.hasMoreElements()) {
			// // LatestConfigSpec configSpec = new LatestConfigSpec();
			// // qr = configSpec.process(qr);
			//// part = (WTPart) qr.nextElement();
			// }

		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new WTException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new WTException(e);
		} finally {
			close(is);
		}
		System.out.println("数据导出成功");
	}

	public static List<String> getCascade(String STATUTE_PATHs) throws WTException {

		System.out.println("===>>start==>>writeToExcel===>>");
		InputStream is = null;
		OutputStream on = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Cell cell1;

			String tempVal;

			// 第一行为表头，从第二行开始遍历
			for (int i = 1; i < 10; i++) {
				Row row = sheet.createRow(i);
				for (int k = 0; k <= 10; k++) {
					Cell cell = null;
					cell = row.createCell(k);
					// 在一行内循环
					// Cell first = row.createCell(0);
					// first.setCellValue("111");
					//
					// Cell second = row.createCell(1);
					// second.setCellValue("222");
					//
					// Cell third = row.createCell(2);
					// third.setCellValue("33");

					cell.setCellValue("111");

					cell.setCellValue("222");

					cell.setCellValue("333");

					cell.setCellValue("444");

					cell.setCellValue("555");

				}

			}
			on = new FileOutputStream(STATUTE_PATH);
			workbook.write(on);

			System.out.println("excelList====>>>" + excelList);
			return excelList;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new WTException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new WTException(e);
		} finally {
			close(is);
		}
	}

	/***
	 * 获取系统中所有半成品
	 */
	public static void getWtpartByIbaValues(String partType) {
		System.out.println("===>>start==>>writeSemiPartToExcel===>>" + partType);

		InputStream is = null;
		OutputStream on = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Cell cell1;

			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);
			Timestamp date = Timestamp.valueOf("2019-9-15 00:00:00");	
			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, partType);
			// IBA 属性过滤
			System.out.println();
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			SearchCondition sc7 = new SearchCondition(WTPart.class, "thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL, date);
			qs.appendAnd();
			qs.appendSearchCondition(sc7);
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
			// 第一行为表头，从第二行开始遍历
			for (int i = 1; i <= qr.size(); i++) {
				System.out.println("=====已写入 第" + i + "条数据====");
				Row row = sheet.createRow(i);
				part = (WTPart) qr.nextElement();
				// part = BOMHelp.getPart("T1420B051", "", "Design");
				System.out.println("part======>>>" + part.getNumber());
				IBAUtil iba = new IBAUtil(part);

				List<String> effectiveSite = IbaAttributeUtil.getAttributeValues(part, "partNumberEffectiveSite");
				String synchysis = iba.getIBAValue("synchysis"); // 是否混版及混版尺寸[格式如：否/是（12.3、8.0、4.2、4.0、4.4 混版）]:
				String partApplicationReason = iba.getIBAValue("partApplicationReason"); // 申请原因
				String partNumberEffectiveSite = iba.getIBAValue("partNumberEffectiveSite"); // 料号生效产地
				String otherorigin = iba.getIBAValue("otherorigin"); // 其他生效地及源类型
				String SemiFinishedGoodType = iba.getIBAValue("SemiFinishedGoodType"); // 半成品分类
				String partSingleGlassThickness = iba.getIBAValue("partSingleGlassThickness"); // 单玻璃厚度
				String finishedGoodSize = iba.getIBAValue("finishedGoodSize"); // 产品尺寸（inch）[尺寸保留两位小数，例如：5.13、4.30等
				String finishedGoodLineBody = iba.getIBAValue("finishedGoodLineBody"); // 线体 (指ARRAY的产地)
				String cuttingMode = iba.getIBAValue("cuttingMode"); // 切割方式
				String processingModeAndThickness = iba.getIBAValue("processingModeAndThickness"); // 加工方式及薄化前后厚度
				String Integration = iba.getIBAValue("Integration"); // 集成度(成品/半成品上LCD的小粒片数；对于PCS单位，集成度为1；对于SHT单位，集成度=LCD排版数；对于EA单位，集成度=中片集成度；只能填整数):
				String finishedGoodType = iba.getIBAValue("finishedGoodType"); // 成品型号
				String partDrawingNumber = iba.getIBAValue("partDrawingNumber"); // 图纸编号/工艺文件编号（申请T1时必填，如：TM033HS01)）
				String partInspectionStandard = iba.getIBAValue("partInspectionStandard"); // 检验标准
				String partSupplier = iba.getIBAValue("partSupplier"); // 供应商
				String partEnvProtectionCategory = iba.getIBAValue("partEnvProtectionCategory"); // 环保类别
				String partNote = iba.getIBAValue("partNote"); // 备注说明
				String Coatingtype = iba.getIBAValue("Coatingtype"); // 镀膜类型（非C7/C8半成品请选NA）
				String polarizerType = iba.getIBAValue("polarizerType"); // 偏光片类型
				String specialMonitor = iba.getIBAValue("specialMonitor"); // 是否专显
				String partRFQNumber = iba.getIBAValue("partRFQNumber"); // RFQ编号
				String MtlNumofLargePiece = iba.getIBAValue("MtlNumofLargePiece"); // 对应大张料号
				String absorptionShaftAngle = iba.getIBAValue("absorptionShaftAngle"); // 吸收轴角度(与长边逆时针方向夹角，注：长边为靠近source
																						// IC侧的边):
				String semiFinishedGoodMainSpecificat = iba.getIBAValue("semiFinishedGoodMainSpecificatio"); // 主要规格（长 ×
																												// 宽 ×
																												// 厚mm）:
				String cuttingNumber = iba.getIBAValue("cuttingNumber"); // 切数(例如：1/36)
				String surfaceTreatment = iba.getIBAValue("surfaceTreatment"); // 表面处理方式
				String materialClassification = iba.getIBAValue("materialClassification");
				String polaroidType = iba.getIBAValue("polaroidType");// 物料分级
				String effectiveSites = "";
				String partState = part.getLifeCycleState().getDisplay(Locale.CHINA);
				String classifiName = CreatePartClassificationUtil.getClassification(part);
				if (effectiveSite != null) {
					for (String str : effectiveSite) {
						effectiveSites += str;
					}
				}

				for (int k = 0; k <= 34; k++) {
					// 在一行内循环
					Cell first = row.createCell(0);
					first.setCellValue(part.getNumber());

					Cell second = row.createCell(1);
					second.setCellValue(part.getName());

					Cell third = row.createCell(2);
					third.setCellValue(part.getSource().toString());

					Cell cell4 = row.createCell(3);
					cell4.setCellValue(part.getViewName());

					Cell cell5 = row.createCell(4);
					cell5.setCellValue(part.getDefaultUnit().toString());

					Cell cell6 = row.createCell(5);
					cell6.setCellValue(partState);

					Cell cell7 = row.createCell(6);
					cell7.setCellValue(classifiName);

					Cell cell8 = row.createCell(7);
					cell8.setCellValue(synchysis);

					Cell cell9 = row.createCell(8);
					cell9.setCellValue(partApplicationReason);

					Cell cell10 = row.createCell(9);
					cell10.setCellValue(effectiveSites);

					Cell cell11 = row.createCell(10);
					cell11.setCellValue(otherorigin);

					Cell cell12 = row.createCell(11);
					cell12.setCellValue(SemiFinishedGoodType);

					Cell cell13 = row.createCell(12);
					cell13.setCellValue(partSingleGlassThickness);

					Cell cell14 = row.createCell(13);
					cell14.setCellValue(finishedGoodSize);

					Cell cell15 = row.createCell(14);
					cell15.setCellValue(finishedGoodLineBody);

					Cell cell16 = row.createCell(15);
					cell16.setCellValue(cuttingMode);

					Cell cell17 = row.createCell(16);
					cell17.setCellValue(processingModeAndThickness);

					Cell cell18 = row.createCell(17);
					cell18.setCellValue(Integration);

					Cell cell19 = row.createCell(18);
					cell19.setCellValue(finishedGoodType);

					Cell cell20 = row.createCell(19);
					cell20.setCellValue(partDrawingNumber);

					Cell cell21 = row.createCell(20);
					cell21.setCellValue(partInspectionStandard);

					Cell cell22 = row.createCell(21);
					cell22.setCellValue(partSupplier);

					Cell cell23 = row.createCell(22);
					cell23.setCellValue(partEnvProtectionCategory);

					Cell cell24 = row.createCell(23);
					cell24.setCellValue(partNote);

					Cell cell25 = row.createCell(24);
					cell25.setCellValue(Coatingtype);

					Cell cell26 = row.createCell(25);
					cell26.setCellValue(polarizerType);

					Cell cell27 = row.createCell(26);
					cell27.setCellValue(specialMonitor);

					Cell cell28 = row.createCell(27);
					cell28.setCellValue(partRFQNumber);

					Cell cell29 = row.createCell(28);
					cell29.setCellValue(MtlNumofLargePiece);

					Cell cell30 = row.createCell(29);
					cell30.setCellValue(absorptionShaftAngle);
					Cell cell31 = row.createCell(30);
					cell31.setCellValue(semiFinishedGoodMainSpecificat);

					Cell cell32 = row.createCell(31);
					cell32.setCellValue(cuttingNumber);

					Cell cell33 = row.createCell(32);
					cell33.setCellValue(surfaceTreatment);

					Cell cell34 = row.createCell(33);
					cell34.setCellValue(materialClassification);
					Cell cell35 = row.createCell(34);
					cell35.setCellValue(polaroidType);

				}
				Map<String, String> map = getClassifictionByPart(part);
				;
				int count = 33;
				for (Map.Entry<String, String> mapParts : map.entrySet()) {
					count = count + 1;
					System.out.println("start  into ===>>");
					Cell cell = null;
					System.out.println("key ===>>" + mapParts.getKey());
					System.out.println("mapParts.getValue()1===>>" + mapParts.getValue());
					cell = row.createCell(count);
					cell.setCellValue(mapParts.getKey() + ":" + mapParts.getValue());
				}

			}
			on = new FileOutputStream(STATUTE_PATH);
			workbook.write(on);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WTException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(is);
		}
		System.out.println("数据导出成功");
	}

	/***
	 * 获取系统中所有原材料
	 * 
	 * @param partType
	 *            物料类别
	 */
	public static void getWtpartByIbaValuesRawMaterial(String partType) {
		System.out.println("===>>start==>>writeSemiPartToExcel1===>>" + partType);
		InputStream is = null;
		OutputStream on = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Cell cell1;

			String tempVal;
			View view = ViewHelper.service.getView("Design");
			ObjectIdentifier objId = PersistenceHelper.getObjectIdentifier(view);
			Timestamp date = Timestamp.valueOf("2019-9-15 00:00:00");	
			QuerySpec qs = new QuerySpec(WTPart.class);
			qs.setAdvancedQueryEnabled(true);
			QueryUtil.appendTypeCondition(qs, partType);
			// IBA 属性过滤
			
			qs.appendAnd();
			int iIndex = qs.getFromClause().getPosition(WTPart.class);
			SearchCondition sc = new SearchCondition(WTPart.class, ViewManageable.VIEW + "." + ObjectReference.KEY,
					SearchCondition.EQUAL, objId);
			qs.appendWhere(sc, new int[] { iIndex });
			SearchCondition sc7 = new SearchCondition(WTPart.class, "thePersistInfo.createStamp", SearchCondition.GREATER_THAN_OR_EQUAL, date);
			qs.appendAnd();
			qs.appendSearchCondition(sc7);
			qs.appendAnd();
			qs.appendWhere(new SearchCondition(new KeywordExpression("A0.latestiterationinfo"), SearchCondition.EQUAL,
					new KeywordExpression("1")));
			QueryResult qr = PersistenceHelper.manager.find(qs);
			System.out.println("stmtSpec====>>>" + qs);
			System.out.println("=====>>" + qr.size());
			WTPart part = null;
			// 第一行为表头，从第二行开始遍历
			for (int i = 1; i <= qr.size(); i++) {
				System.out.println("=====已写入 第" + i + "条数据====");
				Row row = sheet.createRow(i);
				part = (WTPart) qr.nextElement();
				// part = BOMHelp.getPart("T1420B051", "", "Design");
				System.out.println("part======>>>" + part.getNumber());
				IBAUtil iba = new IBAUtil(part);

				List<String> effectiveSite = IbaAttributeUtil.getAttributeValues(part, "partNumberEffectiveSite");
				String partApplicationReason = iba.getIBAValue("partApplicationReason"); // 申请人及申请原因
				String partCName = iba.getIBAValue("partCName"); // 物料中英文名称
				String partEnvProtectionCategory = iba.getIBAValue("partEnvProtectionCategory");// 环保类别
				String finishedGoodType = iba.getIBAValue("finishedGoodType"); // 产品型号
				String partDrawingNumber = iba.getIBAValue("partDrawingNumber"); // 材料型号/图纸编号
				String partInspectionStandard = iba.getIBAValue("partInspectionStandard"); // 检验标准
				String partMainSpecification = iba.getIBAValue("partMainSpecification"); // 主要规格
				String partNote = iba.getIBAValue("partNote"); // 备注说明
				// String partNumberEffectiveSite= iba.getIBAValue("partNumberEffectiveSite");
				// // 料号生效产地
				String partRFQNumber = iba.getIBAValue("partRFQNumber"); // RFQ编号
				String partSupplier = iba.getIBAValue("partSupplier"); // 供应商
				String specialMonitor = iba.getIBAValue("specialMonitor"); // 是否专显
				String departments = iba.getIBAValue("departments"); // 所属部门
				String materialClassification = iba.getIBAValue("materialClassification"); // 物料分级
				String partRecognize = iba.getIBAValue("partRecognize"); // 是否需要零件承认
				String absorptionShaftAngle = iba.getIBAValue("absorptionShaftAngle"); // 吸收轴角度(与长边逆时针方向夹角，注：长边为靠近source
																						// IC侧的边):
				String polarizerType = iba.getIBAValue("polarizerType"); // 偏光片类型
				String surfaceTreatment = iba.getIBAValue("surfaceTreatment"); // 表面处理方式
				String partSize = iba.getIBAValue("partSize"); // 尺寸(模组尺寸，例如：5.13、4.30等):
				String MtlNumofPOLBtn = iba.getIBAValue("MtlNumofPOLBtn"); // 对应下POL料号(若已有料号，务必填写正确，涉及关联mark):
				String isBothNewPolarizer = iba.getIBAValue("isBothNewPolarizer"); // 上下偏光片是否均为新增(务必确认上下偏一同申请还是仅单侧申请):
				String POLResolution = iba.getIBAValue("POLResolution");
				String MtlNumofPOLTop = iba.getIBAValue("MtlNumofPOLTop");
				String MtlNumofPOLBtns = iba.getIBAValue("MtlNumofPOLBtn");
				String DisplayMode = iba.getIBAValue("DisplayMode");
				String effectiveSites = "";
				String partState = part.getLifeCycleState().getDisplay(Locale.CHINA);
				String classifiName = CreatePartClassificationUtil.getClassification(part);
				if (effectiveSite != null) {
					for (String str : effectiveSite) {
						effectiveSites += str;
					}
				} else {
					effectiveSites = "*";
				}

				for (int k = 0; k <= 34; k++) {
					// 在一行内循环
					Cell first = row.createCell(0);
					first.setCellValue(part.getNumber());

					Cell second = row.createCell(1);
					second.setCellValue(part.getName());

					Cell third = row.createCell(2);
					third.setCellValue(part.getSource().toString());

					Cell cell4 = row.createCell(3);
					cell4.setCellValue(part.getViewName());

					Cell cell5 = row.createCell(4);
					cell5.setCellValue(part.getDefaultUnit().toString());

					Cell cell6 = row.createCell(5);
					cell6.setCellValue(classifiName);

					Cell cell7 = row.createCell(6);
					cell7.setCellValue(partApplicationReason);

					Cell cell8 = row.createCell(7);
					cell8.setCellValue(partEnvProtectionCategory);

					Cell cell9 = row.createCell(8);
					cell9.setCellValue(finishedGoodType);

					Cell cell10 = row.createCell(9);
					cell10.setCellValue(partDrawingNumber);

					Cell cell11 = row.createCell(10);
					cell11.setCellValue(partInspectionStandard);

					Cell cell12 = row.createCell(11);
					cell12.setCellValue(partMainSpecification);

					Cell cell13 = row.createCell(12);
					cell13.setCellValue(partNote);

					Cell cell14 = row.createCell(13);
					cell14.setCellValue(effectiveSites);

					Cell cell15 = row.createCell(14);
					cell15.setCellValue(partRFQNumber);

					Cell cell16 = row.createCell(15);
					cell16.setCellValue(partSupplier);

					Cell cell17 = row.createCell(16);
					cell17.setCellValue(partCName);

					Cell cell18 = row.createCell(17);
					cell18.setCellValue(specialMonitor);

					Cell cell19 = row.createCell(18);
					cell19.setCellValue(materialClassification);

					Cell cell20 = row.createCell(19);
					cell20.setCellValue(DisplayMode);

					Cell cell21 = row.createCell(20);
					cell21.setCellValue(POLResolution);

					Cell cell22 = row.createCell(21);
					cell22.setCellValue(partSize);

					Cell cell23 = row.createCell(22);
					cell23.setCellValue(MtlNumofPOLTop);

					Cell cell24 = row.createCell(23);
					cell24.setCellValue(MtlNumofPOLBtn);
					Cell cell25 = row.createCell(24);
					cell25.setCellValue(partState);

				}
				Map<String, String> map = getClassifictionByPart(part);
				;
				int count = 24;
				for (Map.Entry<String, String> mapParts : map.entrySet()) {
					count = count + 1;
					System.out.println("start  into ===>>");
					Cell cell = null;
					System.out.println("key ===>>" + mapParts.getKey());
					System.out.println("mapParts.getValue()1===>>" + mapParts.getValue());
					cell = row.createCell(count);
					cell.setCellValue(mapParts.getKey() + ":" + mapParts.getValue());
				}

			}
			on = new FileOutputStream(STATUTE_PATH);
			workbook.write(on);
			// while (qr.hasMoreElements()) {
			// // LatestConfigSpec configSpec = new LatestConfigSpec();
			// // qr = configSpec.process(qr);
			//// part = (WTPart) qr.nextElement();
			// }

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WTException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(is);
		}
		System.out.println("数据导出成功");
	}

	/***
	 * 更改系统物料状态
	 */
	public static void updatePartIbaByExcel() {

		System.out.println("===>>start==>>change part state ===>>");
		InputStream is = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + PART_STATUTE_PATH);
			is = new FileInputStream(PART_STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Row row;
			Cell cell1;
			String tempVal;

			// 第一行为表头，从第二行开始遍历
			for (int i = 1, rowNum = sheet.getLastRowNum(); i <= rowNum; i++) {
				List<WTPart> partList = new ArrayList<WTPart>();
				if ((row = sheet.getRow(i)) != null) {
					// 遍历列
					int cellNum = row.getLastCellNum();
					String str = "";
					cell1 = row.getCell(0);
					if (cell1 == null || (tempVal = cell1.getStringCellValue()) == null) {
						str = "";
					} else {
						String partNumber = cell1.getStringCellValue();
						str = tempVal.trim();
						System.out.println("===>>str===>>" + str);
						System.out.println("cell1======>>" + cell1.getStringCellValue());
						// part = BOMHelp.getPart(partNumber, "", "");
						WTPart part = getWTPartByNumberAndView(partNumber, "Design");
						if (part != null) {
							part = (WTPart) LifeCycleHelper.service.setLifeCycleState(part,
									State.toState("OBSOLESCENCE"));
							part = (WTPart) PersistenceHelper.manager.refresh(part);
						}
						// if (part != null) {
						// partList = PartWorkflowUtil.getAllPartViews(part);// 获取所有视图
						// partList.add(part);
						// System.out.println("partList===>>>"+partList);
						// for (int j = 0; j < partList.size(); j++) {
						// System.out.println("22====>>"+partList.get(j).getClass());
						// if(partList.get(j) instanceof WTPart) {
						// System.out.println("11====>>");
						// WTPart wtpart = (WTPart)partList.get(j);
						// }
						//
						//
						// }
						// for (WTPart wtpart : partList) {
						// wtpart = (WTPart) LifeCycleHelper.service.setLifeCycleState(wtpart,
						// State.toState("OBSOLESCENCE"));
						// wtpart = (WTPart) PersistenceHelper.manager.refresh(wtpart);
						// }

						// }
						System.out.println("=====>>>设置成功====");
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WTException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(is);
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param closeable
	 */
	public static void close(Closeable closeable) {
		try {
			if (closeable != null) {
				closeable.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param in
	 * @param filename
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkbok(File file) throws IOException {
		Workbook wb = null;
		FileInputStream in = new FileInputStream(file);
		if (file.getName().endsWith(EXCEL_XLS)) { // Excel&nbsp;2003
			wb = new HSSFWorkbook(in);
		} else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 根据编号和视图查询对应最新视图版本的零部件.
	 * 
	 * @author Harry Cao
	 * @param number
	 * @param view
	 * @return
	 * @throws WTException
	 */
	public static WTPart getWTPartByNumberAndView(String number, String view) throws WTException {
		WTPartMaster master = getWTPartMasterByNumber(number);
		if (master == null)
			return null;
		else
			return getWTPartByMasterAndView(master, view);
	}

	/**
	 * 根据零部件的编号查找零部件的主数据(master)
	 * 
	 * @author Harry Cao
	 * @param partNumber
	 * @return
	 * @throws WTException
	 */
	public static WTPartMaster getWTPartMasterByNumber(String partNumber) throws WTException {
		WTPartMaster master = null;
		QuerySpec qs = null;
		QueryResult qr = null;
		qs = new QuerySpec(WTPartMaster.class);
		// 定义搜索条件，以零部件编号方式在master中搜索
		SearchCondition sc = new SearchCondition(WTPartMaster.class, WTPartMaster.NUMBER, SearchCondition.EQUAL,
				partNumber);
		qs.appendSearchCondition(sc);
		qr = PersistenceHelper.manager.find(qs);
		if (qr.hasMoreElements()) {
			master = (WTPartMaster) qr.nextElement();
		}
		return master;
	}

	/**
	 * 根据零部件主数据(master)和视图获取对应最新版本零部件
	 * 
	 * @author Harry Cao
	 * @param master
	 * @param view
	 * @return
	 * @throws WTException
	 */
	public static WTPart getWTPartByMasterAndView(WTPartMaster master, String view) throws WTException {
		WTPart part = null;
		// 根据视图名称获取视图对象
		View viewObj = ViewHelper.service.getView(view);

		// 根据视图构造产品结构配置规范
		WTPartStandardConfigSpec standardConfig = WTPartStandardConfigSpec.newWTPartStandardConfigSpec(viewObj, null);
		try {
			standardConfig.setView(viewObj);
		} catch (WTPropertyVetoException wpve) {
			throw new WTException(wpve);
		}

		// 根据master和视图获取对应最新的视图版本零部件
		QueryResult qr1 = ConfigHelper.service.filteredIterationsOf(master, standardConfig);
		if (qr1.hasMoreElements()) {
			part = (WTPart) qr1.nextElement();
			if (!part.getViewName().equals(view))
				return null;
			System.out.println("部件的视图：" + part.getViewName());
		}

		return part;
	}

	public static Map<String, String> getClassifictionByPart(WTPart part) {
		Map<String, String> strList = new HashMap<String, String>();
		try {
			Hashtable<String, String> values = new Hashtable<String, String>();
			ClassificationNode classificationNode = ClassificationUtil.getClassificationNode(part);
			if (classificationNode != null) {
				System.out.println("start == into ==--------" + classificationNode.getIBAReferenceableDisplayString());
				System.out.println("classificationNodeName===>>" + classificationNode.getName());
				strList = ClassifictionToSourcing.getClassifictionIbas(classificationNode.getName(), part);
				System.out.println("strList===>>" + strList);
			}
		} catch (RemoteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WTException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return strList;
	}

	public static void updatePartClassifiction() throws Exception {
		System.out.println("=====>>>start into ipdate    ");
		WTPart part = BOMHelp.getPart("T1420B051", "", "Design");
		setClassification("wt.part.WTPart", "电阻", part);
		part = (WTPart) PersistenceHelper.manager.refresh(part);

	}
	
	public static void setClassification(String classificationType, String classificationPath, IBAHolder holder)
			throws Exception {

		ReferenceDefinition refDefinition = CreatePartClassificationUtil.getReferenceDefinition(classificationType);
		if (refDefinition == null)
			throw new Exception("No Classification Structure Found for " + classificationType);

		// IBAReferenceable referenceable = getClassificationNodeByPath(
		// classificationType, classificationPath);
		IBAReferenceable referenceable = CreatePartClassificationUtil.getClassificationNodeByName(classificationPath);
		if (referenceable == null)
			throw new Exception("No Classification Path " + classificationPath + " found for " + classificationType);

		ReferenceValue refValue = ReferenceValue.newReferenceValue();
		PersistenceHelper.manager.save(refValue);

	}

}
